package com.tencent.qplus.paddata.stat2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.tencent.qplus.paddata.db.DataSourceManager;
import com.tencent.qplus.paddata.stat.Job;
import com.tencent.qplus.paddata.util.JdbcUtils;

public class StatOnlineTimeJob extends Job {
	
	private String pre8Days;
	private String pre31Days;
	private DecimalFormat dfc = new DecimalFormat("0.00%");
	

	@Override
	public void run() throws SQLException {
		init();
		Map<String,List<String>> versionUinMap = getNewUinByDay(pre8Days);
		statdata(versionUinMap,pre8Days,120,7);
		statdata(versionUinMap,pre8Days,300,7);
		versionUinMap = getNewUinByDay(pre31Days);
		statdata(versionUinMap,pre31Days,120,30);
		statdata(versionUinMap,pre31Days,300,30);

	}

	
	private void statdata(Map<String,List<String>> versionUinMap,String day,int timeLength,int days) throws SQLException{
		String endDay = dateUtils.getDateStrByDaysFromDate(day, days+1);
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement pst1 = null;
		PreparedStatement pst2 = null;
		
		Map<String,Map<Integer,Integer>> logintimesMap = new HashMap<String,Map<Integer,Integer>>();
		
		String insertResultSql = "REPLACE INTO onlinestat(`day`,days,totalNewUV,onlineTimeType,loginVersion,UVNum,rate)VALUES(?,?,?,?,?,?,?)";
		String insertResultSql2 = "REPLACE INTO onlinestatlogintimes(`day`,days,totalNewUV,onlineTimeType,loginVersion,UVNum,loginTimes)VALUES(?,?,?,?,?,?,?)";
		Connection statResultConn = DataSourceManager.getInstance().getDataSource("padversiondatastatdb").getConnection();
		Connection conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
		try {
			st = conn.createStatement();
			pst1 = statResultConn.prepareStatement(insertResultSql);
			pst2 = statResultConn.prepareStatement(insertResultSql2);
			String whereSql = "' and logintime>='"+day+"' and logintime<'"+endDay+"' and onlinetimelength>="+timeLength;
			
			
			for(String version:versionUinMap.keySet()){
				List<String> uinlist = versionUinMap.get(version);
				int versionLoginUin=0;
				int versionTotalNewUin = uinlist.size();
				
				System.out.println("version:"+version+"\tversionTotalNewUin:"+versionTotalNewUin);
				for(String uin:uinlist){
					int index = (int) (Long.parseLong(uin)%100);
					String sql = "select count(1) from uinonlinetime_"+index+" where uin='"+uin+whereSql;
					rs = st.executeQuery(sql);
					if(rs.next()){
						int times = rs.getInt(1);
						if(times>0){
							versionLoginUin++;
							if(logintimesMap.get(version)==null){
								Map<Integer,Integer> timesMap = new HashMap<Integer,Integer>();
								timesMap.put(times, 1);
								logintimesMap.put(version, timesMap);
							}else{
								Map<Integer,Integer> timesMap = logintimesMap.get(version);
								if(timesMap.get(times)!=null){
									int num = timesMap.get(times);
									timesMap.put(times, num+1);
								}else{
									timesMap.put(times, 1);
								}
							}
						}
					}
				}
				pst1.setString(1, day);
				pst1.setInt(2, days);
				pst1.setInt(3, versionTotalNewUin);
				pst1.setInt(4, timeLength);
				pst1.setString(5, version);
				pst1.setInt(6, versionLoginUin);
				pst1.setString(7, dfc.format(versionLoginUin/(double)versionTotalNewUin))
;				pst1.addBatch();
				
				Map<Integer,Integer> timesMap = logintimesMap.get(version);
				if(timesMap!=null){
					System.out.println("timesMap:"+timesMap.size());
					for(Integer times:timesMap.keySet()){
						pst2.setString(1, day);
						pst2.setInt(2, days);
						pst2.setInt(3, versionTotalNewUin);
						pst2.setInt(4, timeLength);
						pst2.setString(5, version);
						pst2.setInt(6, timesMap.get(times));
						pst2.setInt(7, times);
						
						pst2.addBatch();
					}
				}else{
				}
				pst1.executeBatch();
				pst2.executeBatch();
			}
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(rs);
			JdbcUtils.release(st);
			JdbcUtils.release(pst1);
			JdbcUtils.release(pst2);
			JdbcUtils.release(statResultConn);
			JdbcUtils.release(conn);
		}
	}
	
	private Map<String,List<String>> getNewUinByDay(String day) throws SQLException{
		String sql = "select * from newuin where firstLoginDate='"+day+"'";
		logger.info(sql);
		Statement st = null;
		ResultSet rs = null;
		Map<String,List<String>> map = new HashMap<String,List<String>>();
		Connection conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				String version = rs.getString("loginVersion");
				String uin = rs.getString("uin");
				if(map.containsKey(version)){
					map.get(version).add(uin);
				}else{
					List<String> uinlist = new ArrayList<String>();
					uinlist.add(uin);
					map.put(version, uinlist);
				}
			}
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(conn);
		}
		return map;
	}

	@Override
	public void init() throws SQLException {
			pre8Days = dateUtils.getDateStrByDaysFromDate(statDate, -7);
			pre31Days = dateUtils.getDateStrByDaysFromDate(statDate, -30);
	}
	
	public static void main(String[] args){
		StatOnlineTimeJob job = new StatOnlineTimeJob();
		job.stat(args);
	}

}
